package cn.harvest.storage.bo.manager;

import java.util.ArrayList;
import java.util.List;

import cn.harvest.storage.dao.DAO;


public class ReportService {

	private DAO dao = DAO.getDAO();

	private ReportService() {

	}

	public static ReportService getService() {

		ReportService service = new ReportService();
		return service;
	}
	
	public List report1(String begin, String end, String merchandiseType, String merchandiseName ){
		
		//买"11"    还"12"   卖"21"   借"22"
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT info.merchandise_id as merchandise_id,");
		sql.append(" 		info.merchandise_name as merchandise_name,");
		sql.append(" 		info.merchandise_norm as merchandise_norm,");
		sql.append(" 		info.merchandise_amount as merchandise_amount,");
		sql.append(" 		SUM(info.type_11_amount) as in_11_amount,");
		sql.append(" 		SUM(info.type_21_amount) as in_12_amount,");
		sql.append(" 		SUM(info.type_22_amount) as out_21_amount,");
		sql.append(" 		SUM(info.type_12_amount) as out_22_amount");
		sql.append(" FROM (SELECT mer.merchandise_id as merchandise_id,");
		sql.append("			  mer.merchandise_name as merchandise_name,");
		sql.append(" 			  mer.merchandise_norm as merchandise_norm,");
		sql.append(" 			  mer.merchandise_amount as merchandise_amount,");
		sql.append(" 			  CASE WHEN inv.invoice_type_id = 11 THEN SUM(invit.merchandise_amount) END as type_11_amount,");
		sql.append(" 			  CASE WHEN inv.invoice_type_id = 12 THEN SUM(invit.merchandise_amount) END as type_12_amount,");
		sql.append(" 			  CASE WHEN inv.invoice_type_id = 21 THEN SUM(invit.merchandise_amount) END as type_21_amount,");
		sql.append(" 			  CASE WHEN inv.invoice_type_id = 22 THEN SUM(invit.merchandise_amount) END as type_22_amount");
		sql.append("	   FROM invoice inv, invoice_item invit, merchandise mer");
		sql.append("	   WHERE invit.invoice_id = inv.invoice_id");
		sql.append("	   AND STR_TO_DATE(inv.invoice_date, '%Y-%m-%d') >= STR_TO_DATE('" + begin + "', '%Y-%m-%d')");
		sql.append("	   AND STR_TO_DATE(inv.invoice_date, '%Y-%m-%d') <= STR_TO_DATE('" + end + "', '%Y-%m-%d')");
		if (!"".equals(merchandiseType)) {
			sql.append("	   AND mer.merchandise_type_id = '" + merchandiseType + "'");
		}
		if (!"".equals(merchandiseName)) {
			sql.append("	   AND mer.merchandise_name LIKE '%" + merchandiseName + "%'");
		}
		sql.append("	   AND invit.merchandise_id = mer.merchandise_id");
		sql.append("	   GROUP BY mer.merchandise_id, inv.invoice_type_id) info");
		sql.append(" GROUP BY info.merchandise_id");
		
		System.out.println("### - ReportService -> report1 -> #SQL \r\n	 " + sql);

		List resultItems = dao.selectBySQL(sql.toString());
		List resultList = null;
		if (resultItems.size() > 0) {
			resultList = new ArrayList();
			for (int i = 0; i < resultItems.size(); i++) {
				Object[] objArr = (Object[]) resultItems.get(i);
				String[] result = new String[7];
				result[0] = objArr[0].toString();
				result[1] = objArr[1].toString();
				result[2] = objArr[2].toString();
				
				String zero = "0";
				float merchandiseAmount = Float.parseFloat(null == objArr[3] ? zero : objArr[3].toString());
				float inAmount11 = Float.parseFloat(null == objArr[4] ? zero : objArr[4].toString());
				float inAmount12 = Float.parseFloat(null == objArr[5] ? zero : objArr[5].toString());
				float outAmount21 = Float.parseFloat(null == objArr[6] ? zero : objArr[6].toString());
				float outAmount22 = Float.parseFloat(null == objArr[7] ? zero : objArr[7].toString());
				
				result[3] = String.valueOf(merchandiseAmount + (outAmount21 + outAmount22 - inAmount12) - inAmount11);
				result[4] = String.valueOf(inAmount11);
				result[5] = String.valueOf(outAmount21 + outAmount22 - inAmount12);
				result[6] = String.valueOf(merchandiseAmount);
				resultList.add(result);
			}
		}
		
		return resultList;
	}

	public List report2(String begin, String end, String merchandiseType, String merchandiseName ){
		
		//买"11"    还"12"   卖"21"   借"22"
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT info.merchandise_id as merchandise_id,");
		sql.append(" 		info.merchandise_name as merchandise_name,");
		sql.append(" 		info.merchandise_norm as merchandise_norm,");
		sql.append(" 		info.merchandise_amount as merchandise_amount,");
		sql.append(" 		SUM(info.type_11_amount) as in_11_amount,");
		sql.append(" 		SUM(info.type_21_amount) as in_12_amount,");
		sql.append(" 		SUM(info.type_22_amount) as out_21_amount,");
		sql.append(" 		SUM(info.type_12_amount) as out_22_amount");
		sql.append(" FROM (SELECT mer.merchandise_id as merchandise_id,");
		sql.append("			  mer.merchandise_name as merchandise_name,");
		sql.append(" 			  mer.merchandise_norm as merchandise_norm,");
		sql.append(" 			  mer.merchandise_amount as merchandise_amount,");
		sql.append(" 			  CASE WHEN inv.invoice_type_id = 11 THEN SUM(invit.merchandise_amount) END as type_11_amount,");
		sql.append(" 			  CASE WHEN inv.invoice_type_id = 12 THEN SUM(invit.merchandise_amount) END as type_12_amount,");
		sql.append(" 			  CASE WHEN inv.invoice_type_id = 21 THEN SUM(invit.merchandise_amount) END as type_21_amount,");
		sql.append(" 			  CASE WHEN inv.invoice_type_id = 22 THEN SUM(invit.merchandise_amount) END as type_22_amount");
		sql.append("	   FROM invoice inv, invoice_item invit, merchandise mer");
		sql.append("	   WHERE invit.invoice_id = inv.invoice_id");
		sql.append("	   AND STR_TO_DATE(inv.invoice_date, '%Y-%m-%d') >= STR_TO_DATE('" + begin + "', '%Y-%m-%d')");
		sql.append("	   AND STR_TO_DATE(inv.invoice_date, '%Y-%m-%d') <= STR_TO_DATE('" + end + "', '%Y-%m-%d')");
		if (!"".equals(merchandiseType)) {
			sql.append("	   AND mer.merchandise_type_id = '" + merchandiseType + "'");
		}
		if (!"".equals(merchandiseName)) {
			sql.append("	   AND mer.merchandise_name LIKE '%" + merchandiseName + "%'");
		}
		sql.append("	   AND invit.merchandise_id = mer.merchandise_id");
		sql.append("	   GROUP BY mer.merchandise_id, inv.invoice_type_id) info");
		sql.append(" GROUP BY info.merchandise_id");
		
		System.out.println("### - ReportService -> report2 -> #SQL \r\n	 " + sql);

		List resultItems = dao.selectBySQL(sql.toString());
		List resultList = null;
		if (resultItems.size() > 0) {
			resultList = new ArrayList();
			for (int i = 0; i < resultItems.size(); i++) {
				Object[] objArr = (Object[]) resultItems.get(i);
				String[] result = new String[7];
				result[0] = objArr[0].toString();
				result[1] = objArr[1].toString();
				result[2] = objArr[2].toString();
				
				String zero = "0";
				float merchandiseAmount = Float.parseFloat(null == objArr[3] ? zero : objArr[3].toString());
				float inAmount11 = Float.parseFloat(null == objArr[4] ? zero : objArr[4].toString());
				float inAmount12 = Float.parseFloat(null == objArr[5] ? zero : objArr[5].toString());
				float outAmount21 = Float.parseFloat(null == objArr[6] ? zero : objArr[6].toString());
				float outAmount22 = Float.parseFloat(null == objArr[7] ? zero : objArr[7].toString());
				
				result[3] = String.valueOf(merchandiseAmount + (outAmount21 + outAmount22 - inAmount12) - inAmount11);
				result[4] = String.valueOf(inAmount11);
				result[5] = String.valueOf(outAmount21 + outAmount22 - inAmount12);
				result[6] = String.valueOf(merchandiseAmount);
				resultList.add(result);
			}
		}
		
		return resultList;
	}
	public static void main(String[] args) {
//		ReportService r = ReportService.getService();
//		r.report1("2012-01-01", "2012-12-31");
	}
}
